﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace CPPEI.Coolzon.Core.Repositories.SQL
{
    public class SqlAssignedPrivilegeRepository : IAssignedPrivilegeRepository
    {
        public PagedList<Data.AssignedPrivilege> GetAssignedPrivilegeList(DbContext context, int start, int limit)
        {
            try
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
                    {
                        cmd.Connection = (SqlConnection)context.DbConnection;
                        cmd.CommandText = "select * from (select *, ROW_NUMBER() OVER(ORDER BY Id) as rownum from [AssignedPrivilege]) a where a.rownum >= @param1 and a.rownum <= @param2";
                        cmd.Parameters.Add(new SqlParameter("param1", start));
                        cmd.Parameters.Add(new SqlParameter("param2", start + limit));

                        DataSet ds = new DataSet();
                        sda.Fill(ds);
                        List<Data.AssignedPrivilege> assignedPrivilegeList = null;
                        if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
                        {
                            assignedPrivilegeList = new List<Data.AssignedPrivilege>();
                            foreach (DataRow dr in ds.Tables[0].Rows)
                            {
                                Data.AssignedPrivilege assignedPrivilege = new Data.AssignedPrivilege();
                                assignedPrivilege.Id = new Guid(dr["Id"].ToString());
                                assignedPrivilege.ObjectId = new Guid(dr["ObjectId"].ToString());
                                assignedPrivilege.ObjectType = dr["ObjectType"].ToString();
                                assignedPrivilege.ObjectData = dr["ObjectData"].ToString();
                                assignedPrivilege.PrivilegeId = new Guid(dr["PrivilegeId"].ToString());
                                assignedPrivilegeList.Add(assignedPrivilege);
                            }
                            return new PagedList<Data.AssignedPrivilege>(assignedPrivilegeList, start, limit, assignedPrivilegeList.Count);
                        }
                        else
                        {
                            return null;
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public bool CreateAssignedPrivilege(DbContext context, Data.AssignedPrivilege assignedPrivilege)
        {
            try
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    cmd.Connection = (SqlConnection)context.DbConnection;
                    cmd.CommandText = "insert into [AssignedPrivilege](Id, ObjectId, ObjectType, ObjectData, PrivilegeId) values(@paramId, @paramObjectId, @paramObjectType, @paramObjectData, @paramPrivilegeId)";
                    cmd.Parameters.Add(new SqlParameter("paramId", assignedPrivilege.Id));
                    cmd.Parameters.Add(new SqlParameter("paramObjectId", assignedPrivilege.ObjectId));
                    cmd.Parameters.Add(new SqlParameter("paramObjectType", assignedPrivilege.ObjectType));
                    cmd.Parameters.Add(new SqlParameter("paramObjectData", assignedPrivilege.ObjectData));
                    cmd.Parameters.Add(new SqlParameter("paramPrivilegeId", assignedPrivilege.PrivilegeId));

                    int ret = cmd.ExecuteNonQuery();
                    if (ret <= 0)
                    {
                        return false;
                    }
                    return true;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public bool RemoveAssignedPrivilege(DbContext context, Data.AssignedPrivilege assignedPrivilege)
        {
            try
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    cmd.Connection = (SqlConnection)context.DbConnection;
                    cmd.CommandText = "delete from [AssignedPrivilege] where Id = @paramId";
                    cmd.Parameters.Add(new SqlParameter("paramId", assignedPrivilege.Id));

                    int ret = cmd.ExecuteNonQuery();
                    if (ret <= 0)
                    {
                        return false;
                    }
                    return true;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public bool UpdateAssignedPrivilege(DbContext context, Data.AssignedPrivilege assignedPrivilege)
        {
            try
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    cmd.Connection = (SqlConnection)context.DbConnection;
                    cmd.CommandText = "update [AssignedPrivilege] set ObjectId = @paramObjectId, ObjectType = @paramObjectType, ObjectData = @paramObjectData, PrivilegeId = @paramPrivilegeId where Id = @paramId";
                    cmd.Parameters.Add(new SqlParameter("paramId", assignedPrivilege.Id));
                    cmd.Parameters.Add(new SqlParameter("paramObjectId", assignedPrivilege.ObjectId));
                    cmd.Parameters.Add(new SqlParameter("paramObjectType", assignedPrivilege.ObjectType));
                    cmd.Parameters.Add(new SqlParameter("paramObjectData", assignedPrivilege.ObjectData));
                    cmd.Parameters.Add(new SqlParameter("paramPrivilegeId", assignedPrivilege.PrivilegeId));

                    int ret = cmd.ExecuteNonQuery();
                    if (ret <= 0)
                    {
                        return false;
                    }
                    return true;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 通过assignedPrivilegeId获取分配权限对象
        /// </summary>
        /// <param name="context"></param>
        /// <param name="assignedPrivilegeId"></param>
        /// <returns></returns>
        public Data.AssignedPrivilege GetAssignedPrivilege(DbContext context, Guid assignedPrivilegeId)
        {
            try
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
                    {
                        cmd.Connection = (SqlConnection)context.DbConnection;
                        cmd.CommandText = "select * from [AssignedPrivilege] where Id = @paramId";
                        cmd.Parameters.Add(new SqlParameter("paramId", assignedPrivilegeId));

                        DataSet ds = new DataSet();
                        sda.Fill(ds);
                        if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
                        {
                            Data.AssignedPrivilege assignedPrivilege = new Data.AssignedPrivilege();
                            assignedPrivilege.Id = new Guid(ds.Tables[0].Rows[0]["Id"].ToString());
                            assignedPrivilege.ObjectId = new Guid(ds.Tables[0].Rows[0]["ObjectId"].ToString());
                            assignedPrivilege.ObjectType = ds.Tables[0].Rows[0]["ObjectType"].ToString();
                            assignedPrivilege.ObjectData = ds.Tables[0].Rows[0]["ObjectData"].ToString();
                            assignedPrivilege.PrivilegeId = new Guid(ds.Tables[0].Rows[0]["PrivilegeId"].ToString());
                            return assignedPrivilege;
                        }
                        else
                        {
                            return null;
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 通过objectId和privilegeId获取分配权限对象
        /// </summary>
        /// <param name="context"></param>
        /// <param name="objectId"></param>
        /// <param name="privilegeId"></param>
        /// <returns></returns>
        public Data.AssignedPrivilege GetAssignedPrivilege(DbContext context, Guid objectId, Guid privilegeId)
        {
            try
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
                    {
                        cmd.Connection = (SqlConnection)context.DbConnection;
                        cmd.CommandText = "select * from [AssignedPrivilege] where ObjectId = @paramObjectId and PrivilegeId = @paramPrivilegeId";
                        cmd.Parameters.Add(new SqlParameter("paramObjectId", objectId));
                        cmd.Parameters.Add(new SqlParameter("paramPrivilegeId", privilegeId));

                        DataSet ds = new DataSet();
                        sda.Fill(ds);
                        if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
                        {
                            Data.AssignedPrivilege assignedPrivilege = new Data.AssignedPrivilege();
                            assignedPrivilege.Id = new Guid(ds.Tables[0].Rows[0]["Id"].ToString());
                            assignedPrivilege.ObjectId = new Guid(ds.Tables[0].Rows[0]["ObjectId"].ToString());
                            assignedPrivilege.ObjectType = ds.Tables[0].Rows[0]["ObjectType"].ToString();
                            assignedPrivilege.ObjectData = ds.Tables[0].Rows[0]["ObjectData"].ToString();
                            assignedPrivilege.PrivilegeId = new Guid(ds.Tables[0].Rows[0]["PrivilegeId"].ToString());
                            return assignedPrivilege;
                        }
                        else
                        {
                            return null;
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }


        public PagedList<Data.AssignedPrivilege> GetAssignedPrivilegeByObjectId(DbContext context, Guid Objectid)
        {
            try
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
                    {
                        cmd.Connection = (SqlConnection)context.DbConnection;
                        cmd.CommandText = "select * from (select *, ROW_NUMBER() OVER(ORDER BY Id) as rownum from [AssignedPrivilege]) a where a.Objectid = @Objectid";
                        cmd.Parameters.Add(new SqlParameter("Objectid", Objectid));

                        DataSet ds = new DataSet();
                        sda.Fill(ds);
                        List<Data.AssignedPrivilege> assignedPrivilegeList = null;
                        if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
                        {
                            assignedPrivilegeList = new List<Data.AssignedPrivilege>();
                            foreach (DataRow dr in ds.Tables[0].Rows)
                            {
                                Data.AssignedPrivilege assignedPrivilege = new Data.AssignedPrivilege();
                                assignedPrivilege.Id = new Guid(dr["Id"].ToString());
                                assignedPrivilege.ObjectId = new Guid(dr["ObjectId"].ToString());
                                assignedPrivilege.ObjectType = dr["ObjectType"].ToString();
                                assignedPrivilege.ObjectData = dr["ObjectData"].ToString();
                                assignedPrivilege.PrivilegeId = new Guid(dr["PrivilegeId"].ToString());
                                assignedPrivilegeList.Add(assignedPrivilege);
                            }
                            return new PagedList<Data.AssignedPrivilege>(assignedPrivilegeList, 0, int.MaxValue, assignedPrivilegeList.Count);
                        }
                        else
                        {
                            return null;
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public PagedList<Data.AssignedPrivilege> GetAssignedPrivilegeByObjectId(DbContext context, Guid Objectid, string ObjectType)
        {
            try
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
                    {
                        cmd.Connection = (SqlConnection)context.DbConnection;
                        cmd.CommandText = "select * from (select *, ROW_NUMBER() OVER(ORDER BY Id) as rownum from [AssignedPrivilege]) a where a.Objectid = @Objectid and a.objectType = @objectType";
                        cmd.Parameters.Add(new SqlParameter("Objectid", Objectid));
                        cmd.Parameters.Add(new SqlParameter("ObjectType", ObjectType));

                        DataSet ds = new DataSet();
                        sda.Fill(ds);
                        List<Data.AssignedPrivilege> assignedPrivilegeList = null;
                        if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
                        {
                            assignedPrivilegeList = new List<Data.AssignedPrivilege>();
                            foreach (DataRow dr in ds.Tables[0].Rows)
                            {
                                Data.AssignedPrivilege assignedPrivilege = new Data.AssignedPrivilege();
                                assignedPrivilege.Id = new Guid(dr["Id"].ToString());
                                assignedPrivilege.ObjectId = new Guid(dr["ObjectId"].ToString());
                                assignedPrivilege.ObjectType = dr["ObjectType"].ToString();
                                assignedPrivilege.ObjectData = dr["ObjectData"].ToString();
                                assignedPrivilege.PrivilegeId = new Guid(dr["PrivilegeId"].ToString());
                                assignedPrivilegeList.Add(assignedPrivilege);
                            }
                            return new PagedList<Data.AssignedPrivilege>(assignedPrivilegeList, 0, int.MaxValue, assignedPrivilegeList.Count);
                        }
                        else
                        {
                            return null;
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public bool RemoveAssignedPrivilege(DbContext context, Guid objectId)
        {
            try
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    cmd.Connection = (SqlConnection)context.DbConnection;
                    cmd.CommandText = "delete from [AssignedPrivilege] where ObjectId = @paramObjectId";
                    cmd.Parameters.Add(new SqlParameter("paramObjectId", objectId));

                    int ret = cmd.ExecuteNonQuery();
                    if (ret <= 0)
                    {
                        return false;
                    }
                    return true;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
    }
}
